﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace prjKQXS_DLL
{
    public class Report_DAL
    {
        public dsReports getAllHoiDong(dsReports ds, bool isPublic)
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("Select * from DMHOIDONG where isPublic = @isPublic", conn);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.Fill(ds.rptDMHOIDONG);
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public dsReports getAllDonVi(dsReports ds, bool isPublic)
        {            
            SqlConnection conn = new SqlConnection(BienTc.connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter("Select * from DMDONVI where isPublic = @isPublic", conn);
                da.SelectCommand.Parameters.AddWithValue("@isPublic", isPublic);
                da.Fill(ds.rptDMDonVi);
                return ds;
            }
            catch (Exception)
            {
                throw; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        //Đổ dữ liệu vào ds.rptDonVi
        public dsReports reportDungChung(dsReports ds)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_baocao_Company_get+chuoi", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.Fill(ds.rptDonVi);
                return ds;
            }
            catch (Exception ex)
            {
                clsCommon.MsgBox(ex.Message, 0);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public dsReports reportDC(dsReports ds)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(BienTc.connString);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_baocao_Company_get+chuoi", conn);
                da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
                da.Fill(ds.rptDonVi);
                return ds;
            }
            catch (Exception ex)
            {
                clsCommon.MsgBox(ex.Message, 0);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        //Đổ dữ liệu vào ds.rptDMCanBo
        public dsReports loadAllCanBo(dsReports ds, string Ma_DM_DONVI, string sDLCap1 = "")
        {
            SqlConnection conn = new SqlConnection(BienTc.connString);            
            try
            {
                string sql="";

                if (Ma_DM_DONVI == "TC")
                {
                    sql = "Select DMCanBo.Ma_DT, DMCanBo.Ten_DT, DMChucVu.Ten_DT AS TenCV, Tuyen, SDT, isBanRong, Noi_O, SoHD, NgayHD, NgayNopTC, NgayRutTC " +
                        "from DMCANBO INNER JOIN LAIXUAT_CB ON DMCanBo.Ma_DT=LAIXUAT_CB.Ma_CB INNER JOIN DMChucVu ON DMChucVu.Ma_DT=DMCanBo.Ma_DMChucVu " +
                        "WHERE isPublic=1 " + sDLCap1 + " Order by DMCANBO.Ma_DT";
                }
                else
                {
                    sql = "Select DMCanBo.Ma_DT, DMCanBo.Ten_DT, DMChucVu.Ten_DT AS TenCV, Tuyen, SDT, isBanRong, Noi_O, SoHD, NgayHD, NgayNopTC, NgayRutTC " +
                        "from DMCANBO INNER JOIN LAIXUAT_CB ON DMCanBo.Ma_DT=LAIXUAT_CB.Ma_CB INNER JOIN DMChucVu ON DMChucVu.Ma_DT=DMCanBo.Ma_DMChucVu " +
                        "where DMCANBO.isPublic=1 and Ma_DMDonVi = @Ma_DMDonVi " + sDLCap1 +
                        " Order by DMCANBO.Ma_DT";
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (Ma_DM_DONVI != "TC")
                {
                    da.SelectCommand.Parameters.AddWithValue("@Ma_DMDonVi", Ma_DM_DONVI);
                }
                da.Fill(ds.rptDMCanBo);                
            }
            catch (Exception)
            {
                throw; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return ds;
        }


       
    }
}
